﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Zxb.DayWork.Core;

namespace Zxb.DayWork.Logic.del
{
    public class EstateWorker
    {
        private static int TVPInsert(DataTable dt,NorthSouth ns)
        {
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ZxbWriter"].ConnectionString);

            SqlCommand cmd = new SqlCommand("EstateImport", sqlConn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpNew", dt);
            tvpParam.SqlDbType = SqlDbType.Structured;
            tvpParam.TypeName = "dbo.BulkUdtEstate";
            SqlParameter statusParam = cmd.Parameters.AddWithValue("@StatusName", Constant.DicNorthSouth[ns].StatusName);
            statusParam.SqlDbType = SqlDbType.NVarChar;

            try
            {
                sqlConn.Open();
                if (dt != null && dt.Rows.Count != 0)
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.InfoLog.Error(string.Format("错误：{0}, 发生在新房库记录{1}与{2}之间 ", ex.Message, dt.Rows[0]["NewHouseRealEstateID"], dt.Rows[1]["NewHouseRealEstateID"]));
            }
            finally
            {
                sqlConn.Close();
            }

            return GetLastID(ns);
        }

        public static void AutoImport(NorthSouth ns)
        {
            int startid = GetLastID(ns);
            int endid = startid + 1;
            while (startid < endid)
            {
                startid = endid;
                DataTable dt = ImportTableFromNewHouse(ns, startid, 1000);
                endid = TVPInsert(dt, ns);
                Console.WriteLine(ns.ToString() + endid.ToString());
                Zxb.DayWork.Core.Logger.InfoLog.Info(ns.ToString() + endid.ToString());
            }
        }

        public static int GetLastID(NorthSouth ns)
        {
            int result = -1;
            string SiteStatusName = Constant.DicNorthSouth[ns].StatusName;
            Database db = DB.ZxbReader;
            string sql = "SELECT StatusValue from SiteStatus where StatusName=@StatusName";
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            db.AddInParameter(dbCommand, "@StatusName", DbType.String, SiteStatusName);
            int.TryParse(db.ExecuteScalar(dbCommand).ToString(), out result);
            return result;
        }

        public static void AutoUpdate()
        {
            DataTable dt = GetUpdateTableCityID();
            TVPUpdate(dt, "EstateUpdateCityID");

            dt = GetUpdateTableCaseAllHits();
            TVPUpdate(dt, "EstateUpdateCaseAllHits");

            dt = GetUpdateTableCaseListHits();
            TVPUpdate(dt, "EstateUpdateCaseListHits");
        }

        private static void TVPUpdate(DataTable dt,string procedureName)
        {
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ZxbWriter"].ConnectionString);

            SqlCommand cmd = new SqlCommand(procedureName, sqlConn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpNew", dt);
            tvpParam.SqlDbType = SqlDbType.Structured;
            tvpParam.TypeName = "dbo.UdtEstateHitsCity";

            try
            {
                sqlConn.Open();
                if (dt != null && dt.Rows.Count != 0)
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.InfoLog.Error(string.Format("错误：{0}", ex.Message));
            }
            finally
            {
                sqlConn.Close();
            }
        }

        //public static void BulkMove(DataTable dt)
        //{
        //    SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["TaskWriter"].ConnectionString);
        //    SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
        //    bulkCopy.DestinationTableName = "EstateStatisticsTest";
        //    bulkCopy.BatchSize = dt.Rows.Count;
        //    bulkCopy.ColumnMappings.Add("RealEstateID", "RealEstateID");
        //    bulkCopy.ColumnMappings.Add("RealEstateName", "RealEstateName");
        //    bulkCopy.ColumnMappings.Add("CaseAllHits", "CaseAllHits");
        //    bulkCopy.ColumnMappings.Add("CaseListHits", "CaseListHits");
        //    bulkCopy.ColumnMappings.Add("IntoTime", "IntoTime");
        //    bulkCopy.ColumnMappings.Add("Province", "Province");
        //    bulkCopy.ColumnMappings.Add("City", "City");
        //    bulkCopy.ColumnMappings.Add("District", "District");
        //    bulkCopy.ColumnMappings.Add("ComArea", "ComArea");
        //    bulkCopy.ColumnMappings.Add("NorthSouth", "NorthSouth");
        //    bulkCopy.ColumnMappings.Add("NewHouseRealEstateID", "NewHouseRealEstateID");

        //    try
        //    {
        //        sqlConn.Open();
        //        if (dt != null && dt.Rows.Count != 0)
        //        {
        //            bulkCopy.WriteToServer(dt);
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Logger.InfoLog.Error(ex.Message);
        //    }
        //    finally
        //    {
        //        sqlConn.Close();
        //        if (bulkCopy != null)
        //        {
        //            bulkCopy.Close();
        //        }
        //    }
        //}

        public static DataTable GetTableSchemaBulkUdtEstate()
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[]
            { 
                new DataColumn("RealEstateID",typeof(Int64)), 
                new DataColumn("RealEstateName",typeof(string)), 
                new DataColumn("CaseAllHits",typeof(Int64)),
                new DataColumn("CaseListHits",typeof(Int64)), 
                new DataColumn("IntoTime",typeof(DateTime)), 
                new DataColumn("Province",typeof(string)), 
                new DataColumn("City",typeof(string)), 
                new DataColumn("District",typeof(string)), 
                new DataColumn("ComArea",typeof(string)), 
                new DataColumn("NorthSouth",typeof(string)), 
                new DataColumn("NewHouseRealEstateID",typeof(int))
            });
            return dt;
        }

        public static DataTable ImportTableFromNewHouse(NorthSouth ns, int startid, int num)
        {
            DataTable targetTable = GetTableSchemaBulkUdtEstate();

            DataTable dt = null;
            Database sourcedb = Constant.DicNorthSouth[ns].DataBase;
            string sql = string.Format("select top {0} NID,Newcode,N_or_E,ProjName,LivinDate,Province,City,District,ComArea from proj_info where NID>{1} order by NID", num, startid);
            DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
            dt = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                long estID = 0;
                long.TryParse(dr["Newcode"].ToString(), out estID);
                DateTime intoTime = new DateTime(1901, 1, 1);
                DateTime.TryParse(dr["LivinDate"].ToString(), out intoTime);
                DateTime.TryParse(intoTime.ToString("yyyy-MM-dd"), out intoTime);
                int nid = 0;
                int.TryParse(dr["NID"].ToString(), out nid);

                DataRow targetRow = targetTable.NewRow();
                targetRow["RealEstateID"] = estID;
                targetRow["RealEstateName"] = dr["ProjName"].ToString();
                targetRow["CaseAllHits"] = 0L;
                targetRow["CaseListHits"] = 0L;
                targetRow["IntoTime"] = intoTime;
                targetRow["Province"] = dr["Province"].ToString();
                targetRow["City"] = dr["City"].ToString();
                targetRow["District"] = dr["District"].ToString();
                targetRow["ComArea"] = dr["ComArea"].ToString();
                targetRow["NorthSouth"] = ns.ToString();
                targetRow["NewHouseRealEstateID"] = nid;
                targetTable.Rows.Add(targetRow);
            }
            return targetTable;
        }

        public static DataTable GetTableSchemaUdtEstateHits()
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[]
            { 
                new DataColumn("CaseAllHits",typeof(Int64)),
                new DataColumn("CaseListHits",typeof(Int64)), 
                new DataColumn("City",typeof(string)), 
                new DataColumn("CityID",typeof(int)),
                new DataColumn("RealEstateID",typeof(long))
            });
            return dt;
        }

        private static DataTable GetUpdateTableCityID()
        {
            DataTable targetTable = GetTableSchemaUdtEstateHits();

            DataTable dt = GetDtCity();
            foreach (DataRow dr in dt.Rows)
            {
                DataRow targetRow = targetTable.NewRow();
                targetRow["City"] = dr["CityName"].ToString();
                targetRow["CityID"] = (int)dr["id"];
                targetTable.Rows.Add(targetRow);
            }

            return targetTable;
        }
        private static DataTable GetDtCity()
        {
            DataTable dt = null;
            Database sourcedb = DB.TaskReader;
            string sql = "select id,CityName from city";
            DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
            dt = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
            return dt;
        }

        private static DataTable GetUpdateTableCaseAllHits()
        {
            DataTable targetTable = GetTableSchemaUdtEstateHits();

            DataTable dt = GetDtCaseAllHits();
            foreach (DataRow dr in dt.Rows)
            {
                long hitsum = 0L;
                long.TryParse(dr["hitsum"].ToString(), out hitsum);
                long estid = 0L;
                long.TryParse(dr["RealEstateID"].ToString(), out estid);
                DataRow targetRow = targetTable.NewRow();
                targetRow["RealEstateID"] = estid;
                targetRow["CaseAllHits"] = hitsum;
                targetTable.Rows.Add(targetRow);
            }

            return targetTable;
        }
        private static DataTable GetDtCaseAllHits()
        {
            DataTable dt = null;
            Database sourcedb = DB.TaskReader;
            string sql = "select RealEstateID,SUM(hits) as hitsum from Cases where RealEstateID>0 group by RealEstateID";
            DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
            dt = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
            return dt;
        }

        private static DataTable GetUpdateTableCaseListHits()
        {
            DataTable targetTable = GetTableSchemaUdtEstateHits();

            DataTable dt = GetDtCaseListHits();
            foreach (DataRow dr in dt.Rows)
            {
                long hitsum = 0L;
                long.TryParse(dr["hitsum"].ToString(), out hitsum);
                long estid = 0L;
                long.TryParse(dr["EstateID"].ToString(), out estid);
                DataRow targetRow = targetTable.NewRow();
                targetRow["RealEstateID"] = estid;
                targetRow["CaseListHits"] = hitsum;
                targetTable.Rows.Add(targetRow);
            }

            return targetTable;
        }
        private static DataTable GetDtCaseListHits()
        {
            DataTable dt = null;
            Database sourcedb = DB.TaskReader;
            string sql = "select EstateID,COUNT(1) as hitsum from HitsWeiKe_Estate group by EstateID";
            DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
            dt = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
            return dt;
        }

        //public static void UpdateCaseListHits()
        //{
        //    DataTable dtHits = GetDtCaseListHits();
        //    Database db = DB.ZxbWriter;
        //    string upsql = "update EstateStatistics set CaseListHits=@CaseListHits where RealEstateID=@RealEstateID";
        //    foreach (DataRow dr in dtHits.Rows)
        //    {
        //        long hitsum = 0L;
        //        long.TryParse(dr["hitsum"].ToString(), out hitsum);
        //        long estid = 0L;
        //        long.TryParse(dr["EstateID"].ToString(), out estid);
        //        DbCommand upcmd = db.GetSqlStringCommand(upsql);
        //        db.AddInParameter(upcmd, "@CaseListHits", DbType.Int64, hitsum);
        //        db.AddInParameter(upcmd, "@RealEstateID", DbType.Int64, estid);
        //        db.ExecuteNonQuery(upcmd);
        //    }
        //}
        
        //public static void UpdateCaseAllHits()
        //{
        //    DataTable dtHits = GetDtCaseAllHits();
        //    Database db = DB.ZxbWriter;
        //    string upsql = "update EstateStatistics set CaseAllHits=@CaseAllHits where RealEstateID=@RealEstateID";
        //    foreach (DataRow dr in dtHits.Rows)
        //    {
        //        long hitsum = 0L;
        //        long.TryParse(dr["hitsum"].ToString(), out hitsum);
        //        long estid = 0L;
        //        long.TryParse(dr["RealEstateID"].ToString(), out estid);
        //        DbCommand upcmd = db.GetSqlStringCommand(upsql);
        //        db.AddInParameter(upcmd, "@CaseAllHits", DbType.Int64, hitsum);
        //        db.AddInParameter(upcmd, "@RealEstateID", DbType.Int64, estid);
        //        db.ExecuteNonQuery(upcmd);
        //    }
        //}

        //public static void UpdateCityID()
        //{
        //    DataTable dtHits = GetDtCity();
        //    Database db = DB.ZxbWriter;
        //    string upsql = "update EstateStatistics set CityID=@CityID where City=@City";
        //    foreach (DataRow dr in dtHits.Rows)
        //    {
        //        int cityid = 0;
        //        int.TryParse(dr["id"].ToString(), out cityid);
        //        DbCommand upcmd = db.GetSqlStringCommand(upsql);
        //        db.AddInParameter(upcmd, "@CityID", DbType.Int32, cityid);
        //        db.AddInParameter(upcmd, "@City", DbType.String, dr["CityName"].ToString().Trim());
        //        db.ExecuteNonQuery(upcmd);
        //    }
        //}

        
    }
}
